
--sprocUserInsertUpdateSingleItem
--sprocUserSelectSingleItemByEmail
--sprocUserSelectSingleItemIsEmailExist
--sprocUserSelectSingleItemByAuthKey
--sprocUserSelectSingleItem
--sprocUserSelectSingleItemGUID
--sprocUserActivateAccount
--sprocUserResetPassword

--sprocUserProfileInsertUpdateSingleItem
--sprocUserProfileSelectSingleItem

--sprocUserContactInsertUpdateSingleItem
--sprocUserContactSelectSingleItem

--sp_helptext 'sprocUserInsertUpdateSingleItem'

 
--Update tblCEUser Set EInfo1 = ENCRYPTBYKEY(Key_Guid(N'CXUserSym'), Email), EInfo2 = ENCRYPTBYKEY(Key_Guid(N'CXUserSym'), AuthCode) Where Email Like 'e_test1%'

ALTER PROCEDURE [dbo].[sprocUserInsertUpdateSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserInsertUpdateSingleItem]  
 '   DATE CREATED:        1 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          http://www.ComparExpress.com  
 '   FUNCTION:             Creates or updates an item in table: tblCEUser  
 '   IN:                    
 '   OUT:                  Nothing  
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:           
 '   MODIFIED BY:           
 '   REASON MODIFICATION:   
 '===============================================================*/  
 @CEID bigint  
   ,@UserId varchar(50)  
   ,@Email varchar(255)  
   ,@AuthCode varchar(255)  
   ,@AuthSalt varchar(255)  
   ,@AuthHint varchar(255)  
   ,@UsrTypeId int  
   ,@IsAcceptTC bit  
   ,@Status bit  
   ,@Info1 varchar(50)  
   ,@Info2 varchar(50)  
   ,@Info3 varchar(50)  
   ,@Info4 varchar(50)  
   ,@Info5 varchar(50)  
   ,@Info6 varchar(50)  
   ,@Info7 varchar(50)  
   ,@Info8 varchar(50)  
   ,@Info9 varchar(50)  
   ,@Info10 varchar(50)  
   ,@RefCEUserId bigint  
  
 AS  
 
 Open Symmetric Key CXUserSym
 Decryption By Certificate CXUserCert
 
 DECLARE @ReturnValue int  
  
 IF (@CEID = 0) -- New Item  
 BEGIN  
  INSERT INTO [tblCEUser]  
       ([UserId]  
       ,[Email]  
       ,[AuthCode]  
       ,[AuthSalt]  
       ,[AuthHint]  
       ,[UsrTypeId]  
       ,[IsAcceptTC]  
       ,[Status]  
       ,[Info1]  
       ,[Info2]  
       ,[Info3]  
       ,[Info4]  
       ,[Info5]  
       ,[Info6]  
       ,[Info7]  
       ,[Info8]  
       ,[Info9]  
       ,[Info10]  
       ,[RefCEUserId]
       ,[EInfo1]
       ,[EInfo2])  
    VALUES  
       (@UserId  
       ,@Email  
       ,@AuthCode  
       ,@AuthSalt  
       ,@AuthHint  
       ,@UsrTypeId  
       ,@IsAcceptTC  
       ,@Status  
       ,@Info1  
       ,@Info2  
       ,@Info3  
       ,@Info4  
       ,@Info5  
       ,@Info6  
       ,@Info7  
       ,@Info8  
       ,@Info9  
       ,@Info10  
       ,@RefCEUserId
       ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Email)
       ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @AuthCode))  
  
  SELECT @ReturnValue = SCOPE_IDENTITY()  
 END  
 ELSE  
 BEGIN  
  UPDATE [tblCEUser]  
     SET   
      [UserId] = @UserId  
     ,[Email] = @Email  
     ,[AuthCode] = @AuthCode  
     ,[AuthSalt] = @AuthSalt  
     ,[AuthHint] = @AuthHint  
     ,[UsrTypeId] = @UsrTypeId  
     ,[Status] = @Status  
     ,[IsAcceptTC] = @IsAcceptTC  
     ,[Info1] = @Info1  
     ,[Info2] = @Info2  
     ,[Info3] = @Info3  
     ,[Info4] = @Info4  
     ,[Info5] = @Info5  
     ,[Info6] = @Info6  
     ,[Info7] = @Info7  
     ,[Info8] = @Info8  
     ,[Info9] = @Info9  
     ,[Info10] = @Info10  
     ,[RefCEUserId] = @RefCEUserId  
     ,[ModifiedDate] = GetDate()  
     ,[EInfo1] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Email)
	 ,[EInfo2] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @AuthCode) 
  WHERE [CEID] = @CEID  
  
  SELECT @ReturnValue = @CEID  
 END  
 
 CLOSE Symmetric Key CXUserSym
 
 IF (@@ERROR != 0)  
 BEGIN  
  RETURN -1  
 END  
 ELSE  
 BEGIN  
  RETURN @ReturnValue  
 END  
 
GO
ALTER PROCEDURE [dbo].[sprocUserSelectSingleItemByEmail]  
/* '===============================================================  
 '   NAME:                 [sprocUserSelectSingleItem]  
 '   DATE CREATED:        3 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          www.CompareXpress.com  
 '   FUNCTION:             Get the list of item from table: tblCEUser  
 '   IN:                    
 '   OUT:   Nothing           
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE
 '===============================================================*/  
 @Email varchar(255)  
 ,@Status bit  
 ,@UsrTypeId int  
 AS  
  
 BEGIN  
 SET NOCOUNT ON  
  
 Open Symmetric Key CXUserSym
 Decryption By Certificate CXUserCert
 
  SELECT [CEID]  
     ,[UserId]  
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo1])) AS Email
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo2])) AS AuthCode
     ,[AuthSalt]  
     ,[AuthHint]  
     ,[UsrTypeId]  
     ,[IsAcceptTC]  
     ,[Status]  
     ,[Info1]  
     ,[Info2]  
     ,[Info3]  
     ,[Info4]  
     ,[Info5]  
     ,[Info6]  
     ,[Info7]  
     ,[Info8]  
     ,[Info9]  
     ,[Info10]  
     ,[RefCEUserId]  
     ,[CreatedDate]  
     ,[ModifiedDate]  
    FROM [tblCEUser]  
   WHERE   
   (Convert(varchar(255), DECRYPTBYKEY([EInfo1])) = @Email And UsrTypeId = @UsrTypeId And [Status] = @Status)  
  
  Close Symmetric Key CXUserSym
END  

--=====================================================
GO  
--EXEC [sprocUserSelectSingleItemIsEmailExist] 'phyokyawthant@gmail.com', 400
ALTER PROCEDURE [dbo].[sprocUserSelectSingleItemIsEmailExist]  
/* '===============================================================  
 '   NAME:                 [sprocUserSelectSingleItemIsEmailExist]  
 '   DATE CREATED:        3 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          www.CompareXpress.com  
 '   FUNCTION:             Get the list of item from table: tblCEUser  
 '   IN:                    
 '   OUT:   Nothing           
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE  
 '===============================================================*/  
 @Email varchar(255)  
 ,@UsrTypeId int  
 AS  
  
 BEGIN  
 SET NOCOUNT ON  
  
 Open Symmetric Key CXUserSym
 Decryption By Certificate CXUserCert
 
  SELECT [CEID]  
     ,[UserId]  
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo1])) AS Email
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo2])) AS AuthCode  
     ,[AuthSalt]  
     ,[AuthHint]  
     ,[UsrTypeId]  
     ,[IsAcceptTC]  
     ,[Status]  
     ,[Info1]  
     ,[Info2]  
     ,[Info3]  
     ,[Info4]  
     ,[Info5]  
     ,[Info6]  
     ,[Info7]  
     ,[Info8]  
     ,[Info9]  
     ,[Info10]  
     ,[RefCEUserId]  
     ,[CreatedDate]  
     ,[ModifiedDate]  
    FROM [tblCEUser]  
   WHERE   
   (Convert(varchar(255), DECRYPTBYKEY([EInfo1])) = @Email And UsrTypeId = @UsrTypeId)  
   
   Close Symmetric Key CXUserSym
END  
  --===========================================================
GO
ALTER PROCEDURE [dbo].[sprocUserSelectSingleItemByAuthKey]  
/* '===============================================================  
 '   NAME:                 [sprocUserSelectSingleItem]  
 '   DATE CREATED:        07 July 2010  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          http://www.ComparExpress.com  
 '   FUNCTION:             Creates or updates an item in table: tblCEUser  
 '   IN:                    
 '   OUT:                  Nothing  
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE 
 '===============================================================*/  
 @AuthKey varchar(255),  
 @GUID varchar(255)  
 AS  
 
 Open Symmetric Key CXUserSym
 Decryption By Certificate CXUserCert
 
 Select CEID, UserId, Email, UsrTypeId, Convert(varchar(255), DECRYPTBYKEY([EInfo2])) AS AuthCode, AuthSalt, RefCEUserId, Status from tblCEUser   
 Where [rowguid] = @GUID AND [AuthSalt] = @AuthKey AND Status = 1  
 
 Close Symmetric Key CXUserSym
--======================================================================


GO
ALTER PROCEDURE [dbo].[sprocUserSelectSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserSelectSingleItem]  
 '   DATE CREATED:        3 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          www.CompareXpress.com  
 '   FUNCTION:             Get the list of item from table: tblCEUser  
 '   IN:                    
 '   OUT:   Nothing           
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE 
 '===============================================================*/  
 @CEID   bigint  
 AS  
  
 BEGIN  
 SET NOCOUNT ON  
  
 Open Symmetric Key CXUserSym
 Decryption By Certificate CXUserCert
 
  SELECT [CEID]  
     ,[UserId]  
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo1])) AS Email  
     ,Convert(varchar(255), DECRYPTBYKEY([EInfo2])) AS AuthCode
     ,[AuthSalt]  
     ,[AuthHint]  
     ,[UsrTypeId]  
     ,[IsAcceptTC]  
     ,[Status]  
     ,[Info1]  
     ,[Info2]  
     ,[Info3]  
     ,[Info4]  
     ,[Info5]  
     ,[Info6]  
     ,[Info7]  
     ,[Info8]  
     ,[Info9]  
     ,[Info10]  
     ,[RefCEUserId]  
     ,[CreatedDate]  
     ,[ModifiedDate]  
    FROM [tblCEUser]  
   WHERE   
   ([CEID] = @CEID)  
   
   Close Symmetric Key CXUserSym
END  


--============================================
GO

ALTER PROCEDURE [dbo].[sprocUserResetPassword]
/*	'===============================================================
	'   NAME:                	[sprocUserResetPassword]
	'   DATE CREATED:       	14 Jun 2009
	'   CREATED BY:          	Phyo Kyaw Thant
	'   CREATED FOR:         	http://www.ComparExpress.com
	'   FUNCTION:            	Creates or updates an item in table: tblCEUser
	'   IN:                  
	'   OUT:                 	Nothing
	'   VERSION: 1           
	'   EXAMPLE:             
	'   COMMENTS:            
	 '   MODIFIED ON:          31/Aug/2010
	 '   MODIFIED BY:          Phyo Kyaw Thant
	 '   REASON MODIFICATION:  TDE
	'===============================================================*/
	@NewPassword varchar(255),
	@AuthKey varchar(255),
	@GUID varchar(255)
	AS
	  
	Open Symmetric Key CXUserSym
	Decryption By Certificate CXUserCert
 
	DECLARE @ReturnValue int

	Update tblCEUser Set [EInfo2] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),@NewPassword), [AuthSalt] = '' Where [rowguid] = @GUID AND [AuthSalt]= @AuthKey
	
	Close Symmetric Key CXUserSym
	
	SELECT @ReturnValue = @@ROWCOUNT
	
	IF (@@ERROR != 0)
	BEGIN
		RETURN -1
	END
	ELSE
	BEGIN
		RETURN @ReturnValue
	END


--=========================================================================
GO
ALTER PROCEDURE [dbo].[sprocUserProfileInsertUpdateSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserProfileInsertUpdateSingleItem]  
 '   DATE CREATED:        1 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          http://www.ComparExpress.com  
 '   FUNCTION:             Creates or updates an item in table: tblCEUsrProfile  
 '   IN:                    
 '   OUT:                  Nothing  
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE 
 '===============================================================*/  
  
 @ProfileID bigint   
 ,@Salutation varchar(5)   
 ,@FirstName varchar(100)   
 ,@SurName varchar(100)   
 ,@DOB datetime  
 ,@Gender varchar(1)   
 ,@MaritalStatus smallint  
 ,@OccupationType smallint  
 ,@Occupation int  
 ,@IDNOType varchar(25)   
 ,@IDNO varchar(50)   
 ,@HighEducation varchar(50)  
 ,@Nationality varchar(50)   
 ,@MontlyIncome float   
 ,@OtherIncome float   
 ,@JobNature varchar(50)  
 ,@YearDriveSg tinyint  
 ,@RefCEUserId bigint  
 ,@Status varchar(20)   
 ,@Info1 varchar(50)   
 ,@Info2 varchar(50)   
 ,@Info3 varchar(50)   
 ,@Info4 varchar(50)   
 ,@Info5 varchar(50)   
 ,@Info6 varchar(50)   
 ,@Info7 varchar(50)   
 ,@Info8 varchar(50)   
 ,@Info9 varchar(50)   
 ,@Info10 varchar(50)   
 ,@Info11 varchar(50)   
 ,@Info12 varchar(50)   
 ,@Info13 varchar(50)   
 ,@Info14 varchar(50)   
 ,@Info15 varchar(50)   
 ,@Info16 varchar(50)   
 ,@Info17 varchar(50)   
 ,@Info18 varchar(50)   
 ,@Info19 varchar(50)   
 ,@Info20 varchar(50)  
  
 AS  
 
	Open Symmetric Key CXUserSym
	Decryption By Certificate CXUserCert
	
	 DECLARE @ReturnValue int  
	  
	 IF (@ProfileID = 0) -- New Item  
	 BEGIN  
	  INSERT INTO [tblCEUsrProfile]  
		   ([Salutation]  
		   ,[FirstName]  
		   ,[SurName]  
		   ,[DOB]  
		   ,[Gender]  
		   ,[MaritalStatus]  
		   ,[OccupationType]  
		   ,[Occupation]  
		   ,[IDNOType]  
		   ,[IDNO]  
		   ,[HighEducation]  
		   ,[Nationality]  
		   ,[MontlyIncome]  
		   ,[OtherIncome]  
		   ,[JobNature]  
		   ,[YearDriveSg]  
		   ,[RefCEUserId]  
		   ,[Status]  
		   ,[Info1]  
		   ,[Info2]  
		   ,[Info3]  
		   ,[Info4]  
		   ,[Info5]  
		   ,[Info6]  
		   ,[Info7]  
		   ,[Info8]  
		   ,[Info9]  
		   ,[Info10]  
		   ,[Info11]  
		   ,[Info12]  
		   ,[Info13]  
		   ,[Info14]  
		   ,[Info15]  
		   ,[Info16]  
		   ,[Info17]  
		   ,[Info18]  
		   ,[Info19]  
		   ,[Info20]
		   ,[EInfo1]
		   ,[EInfo2]
		   ,[EInfo3]
		   ,[EInfo4]
		   ,[EInfo5])  
		VALUES  
		   (@Salutation   
		   ,@FirstName  
		   ,@SurName   
		   ,@DOB  
		   ,@Gender  
		   ,@MaritalStatus  
		   ,@OccupationType  
		   ,@Occupation  
		   ,@IDNOType  
		   ,@IDNO  
		   ,@HighEducation  
		   ,@Nationality  
		   ,@MontlyIncome  
		   ,@OtherIncome  
		   ,@JobNature  
		   ,@YearDriveSg  
		   ,@RefCEUserId  
		   ,@Status  
		   ,@Info1  
		   ,@Info2  
		   ,@Info3  
		   ,@Info4  
		   ,@Info5  
		   ,@Info6  
		   ,@Info7  
		   ,@Info8   
		   ,@Info9   
		   ,@Info10   
		   ,@Info11  
		   ,@Info12  
		   ,@Info13   
		   ,@Info14   
		   ,@Info15   
		   ,@Info16   
		   ,@Info17   
		   ,@Info18   
		   ,@Info19   
		   ,@Info20
		   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @FirstName)
		   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @SurName)
		   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @IDNOType)
		   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @IDNO)
		   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Nationality))  
	  
	  SELECT @ReturnValue = SCOPE_IDENTITY()  
	 END  
	 ELSE  
	 BEGIN  
	  UPDATE [tblCEUsrProfile]  
		  SET [Salutation] = @Salutation  
		  ,[FirstName] = @FirstName  
		  ,[SurName] = @SurName  
		  ,[DOB] = @DOB  
		  ,[Gender] = @Gender  
		  ,[MaritalStatus] = @MaritalStatus  
		  ,[OccupationType] = @OccupationType  
		  ,[Occupation] = @Occupation  
		  ,[IDNOType] = @IDNOType  
		  ,[IDNO] = @IDNO  
		  ,[HighEducation] = @HighEducation  
		  ,[Nationality] = @Nationality  
		  ,[MontlyIncome] = @MontlyIncome  
		  ,[OtherIncome] = @OtherIncome  
		  ,[JobNature] = @JobNature  
		  ,[YearDriveSg] = @YearDriveSg  
		  ,[Status] = @Status  
		  ,[RefCEUserId] = @RefCEUserId  
		  ,[ModifiedDate] = GetDate()  
		  ,[Info1] = @Info1  
		  ,[Info2] = @Info2  
		  ,[Info3] = @Info3  
		  ,[Info4] = @Info4  
		  ,[Info5] = @Info5  
		  ,[Info6] = @Info6  
		  ,[Info7] = @Info7  
		  ,[Info8] = @Info8  
		  ,[Info9] = @Info9  
		  ,[Info10] = @Info10  
		  ,[Info11] = @Info11  
		  ,[Info12] = @Info12  
		  ,[Info13] = @Info13  
		  ,[Info14] = @Info14  
		  ,[Info15] = @Info15  
		  ,[Info16] = @Info16  
		  ,[Info17] = @Info17  
		  ,[Info18] = @Info18  
		  ,[Info19] = @Info19  
		  ,[Info20] = @Info20  
		  ,[EInfo1] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @FirstName)
		  ,[EInfo2] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @SurName)
		  ,[EInfo3] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @IDNOType)
		  ,[EInfo4] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @IDNO)
		  ,[EInfo5] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Nationality)
		WHERE [ProfileID] = @ProfileID  
	  
	  SELECT @ReturnValue = @ProfileID  
	 END  
	 
	 Close Symmetric Key CXUserSym
	 
	 IF (@@ERROR != 0)  
	 BEGIN  
	  RETURN -1  
	 END  
	 ELSE  
	 BEGIN  
	  RETURN @ReturnValue  
	 END  
	  
  
  --==================================================================
GO
ALTER PROCEDURE [dbo].[sprocUserProfileSelectSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserSelectSingleItem]  
 '   DATE CREATED:			3 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          http://www.ComparExpress.com  
 '   FUNCTION:             Get the list of item from table: tblCEUsrProfile  
 '   IN:                    
 '   OUT:   Nothing           
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE
 '===============================================================*/  
 @RefCEUserId   bigint  
 AS  
  
 BEGIN  
 SET NOCOUNT ON  
  	Open Symmetric Key CXUserSym
	Decryption By Certificate CXUserCert
	
  SELECT [ProfileID]  
     ,[Salutation]  
     ,CONVERT(varchar(100),DECRYPTBYKEY(EInfo1)) AS FirstName
     ,CONVERT(varchar(100),DECRYPTBYKEY(EInfo2)) AS SurName  
     ,[DOB]  
     ,[Gender]  
     ,[MaritalStatus]  
     ,[OccupationType]  
     ,[Occupation]  
     ,CONVERT(varchar(25), DECRYPTBYKEY(EInfo3)) AS IDNOType  
     ,CONVERT(varchar(50), DECRYPTBYKEY(EInfo4)) AS IDNO  
     ,[HighEducation]  
     ,CONVERT(varchar(50),DECRYPTBYKEY(EInfo5)) AS Nationality  
     ,[MontlyIncome]  
     ,[OtherIncome]  
     ,[JobNature]  
     ,[YearDriveSg]  
     ,[Status]  
     ,[RefCEUserId]  
     ,[CreatedDate]  
     ,[ModifiedDate]  
     ,[Info1]  
     ,[Info2]  
     ,[Info3]  
     ,[Info4]  
     ,[Info5]  
     ,[Info6]  
     ,[Info7]  
     ,[Info8]  
     ,[Info9]  
     ,[Info10]  
     ,[Info11]  
     ,[Info12]  
     ,[Info13]  
     ,[Info14]  
     ,[Info15]  
     ,[Info16]  
     ,[Info17]  
     ,[Info18]  
     ,[Info19]  
     ,[Info20]  
    FROM [tblCEUsrProfile]  
   WHERE   
   ([RefCEUserID] = @RefCEUserId)  
  
  CLOSE Symmetric Key CXUserSym
END  
  
--========================================================================
GO
ALTER PROCEDURE [dbo].[sprocUserContactInsertUpdateSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserContactInsertUpdateSingleItem]  
 '   DATE CREATED:        6 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          http://www.ComparExpress.com  
 '   FUNCTION:             Creates or updates an item in table: [tblCEContact]  
 '   IN:                    
 '   OUT:                  Nothing  
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE  
 '===============================================================*/  
  
 @ContactId bigint   
 ,@Email nvarchar(255)   
 ,@Mobile nvarchar(50)   
 ,@HomePostCode nvarchar(50)   
 ,@OfficePostCode nvarchar(50)   
 ,@HomeUnitNo nvarchar(50)   
 ,@OfficeUnitNo nvarchar(50)   
 ,@HomeLevelNo nvarchar(50)   
 ,@OfficeLevelNo nvarchar(50)   
 ,@HomeStreet nvarchar(50)   
 ,@OfficeStreet nvarchar(50)   
 ,@HomeFax nvarchar(50)   
 ,@OfficeFax nvarchar(50)   
 ,@HomePhone nvarchar(50)   
 ,@OfficePhone nvarchar(50)   
 ,@HomeCity nvarchar(50)   
 ,@HomeCountry nvarchar(50)   
 ,@OfficeCity nvarchar(50)   
 ,@OfficeCountry nvarchar(50)   
 ,@Status bit   
 ,@OtherContact1 nvarchar(255)   
 ,@OtherContact2 nvarchar(255)   
 ,@RefCEUserId bigint   
 ,@Info1 nvarchar(50)   
 ,@Info2 nvarchar(50)   
 ,@Info3 nvarchar(50)   
 ,@Info4 nvarchar(50)   
 ,@Info5 nvarchar(50)   
 ,@Info6 nvarchar(50)   
 ,@Info7 nvarchar(50)   
 ,@Info8 nvarchar(50)   
 ,@Info9 nvarchar(50)   
 ,@Info10 nvarchar(50)   
 ,@Info11 nvarchar(50)   
 ,@Info12 nvarchar(50)   
 ,@Info13 nvarchar(50)   
 ,@Info14 nvarchar(50)   
 ,@Info15 nvarchar(50)   
 ,@Info16 nvarchar(50)   
 ,@Info17 nvarchar(50)   
 ,@Info18 nvarchar(50)   
 ,@Info19 nvarchar(50)   
 ,@Info20 nvarchar(50)  
  
 AS 
  
  	Open Symmetric Key CXUserSym
	Decryption By Certificate CXUserCert
	
 DECLARE @ReturnValue int  
  
 IF (@ContactId = 0) -- New Item  
 BEGIN  
    INSERT INTO [tblCEContact]  
       ([Email]  
       ,[Mobile]  
       ,[HomePostCode]  
       ,[OfficePostCode]  
       ,[HomeUnitNo]  
       ,[OfficeUnitNo]  
       ,[HomeLevelNo]  
       ,[OfficeLevelNo]  
       ,[HomeStreet]  
       ,[OfficeStreet]  
       ,[HomeFax]  
       ,[OfficeFax]  
       ,[HomePhone]  
       ,[OfficePhone]  
       ,[HomeCity]  
       ,[HomeCountry]  
       ,[OfficeCity]  
       ,[OfficeCountry]  
       ,[Status]  
       ,[OtherContact1]  
       ,[OtherContact2]  
       ,[RefCEUserId]  
       ,[Info1]  
       ,[Info2]  
       ,[Info3]  
       ,[Info4]  
       ,[Info5]  
       ,[Info6]  
       ,[Info7]  
       ,[Info8]  
       ,[Info9]  
       ,[Info10]  
       ,[Info11]  
       ,[Info12]  
       ,[Info13]  
       ,[Info14]  
       ,[Info15]  
       ,[Info16]  
       ,[Info17]  
       ,[Info18]  
       ,[Info19]  
       ,[Info20]
       ,[EInfo1]
       ,[EInfo2]
       ,[EInfo3]
       ,[EInfo4]
       ,[EInfo5]
       )  
    VALUES  
       (@Email  
       ,@Mobile  
       ,@HomePostCode  
       ,@OfficePostCode  
       ,@HomeUnitNo  
       ,@OfficeUnitNo  
       ,@HomeLevelNo  
       ,@OfficeLevelNo  
       ,@HomeStreet  
       ,@OfficeStreet  
       ,@HomeFax  
       ,@OfficeFax  
       ,@HomePhone  
       ,@OfficePhone  
       ,@HomeCity  
       ,@HomeCountry  
       ,@OfficeCity  
       ,@OfficeCountry  
       ,@Status  
       ,@OtherContact1  
       ,@OtherContact2  
       ,@RefCEUserId  
       ,@Info1  
       ,@Info2  
       ,@Info3  
       ,@Info4  
       ,@Info5  
       ,@Info6  
       ,@Info7  
       ,@Info8   
       ,@Info9   
       ,@Info10   
       ,@Info11  
       ,@Info12  
       ,@Info13   
       ,@Info14   
       ,@Info15   
       ,@Info16   
       ,@Info17   
       ,@Info18   
       ,@Info19   
       ,@Info20
       ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Email)
	   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Mobile)
	   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @OfficePostCode)
	   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @HomePhone)
	   ,ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @OfficePhone))  
  SELECT @ReturnValue = SCOPE_IDENTITY()  
 END  
 ELSE  
 BEGIN  
  UPDATE [tblCEContact]  
      SET [Email] = @Email  
      ,[Mobile] = @Mobile  
      ,[HomePostCode] = @HomePostCode  
      ,[OfficePostCode] = @OfficePostCode  
      ,[HomeUnitNo] = @HomeUnitNo  
      ,[OfficeUnitNo] = @OfficeUnitNo  
      ,[HomeLevelNo] = @HomeLevelNo  
      ,[OfficeLevelNo] = @OfficeLevelNo  
      ,[HomeStreet] = @HomeStreet  
      ,[OfficeStreet] = @OfficeStreet  
      ,[HomeFax] = @HomeFax  
      ,[OfficeFax] = @OfficeFax  
      ,[HomePhone] = @HomePhone  
      ,[OfficePhone] = @OfficePhone  
      ,[HomeCity] = @HomeCity  
      ,[HomeCountry] = @HomeCountry  
      ,[OfficeCity] = @OfficeCity  
      ,[OfficeCountry] = @OfficeCountry  
      ,[Status] = @Status  
      ,[OtherContact1] = @OtherContact1  
      ,[OtherContact2] = @OtherContact2  
      ,[RefCEUserId] = @RefCEUserId  
      ,[ModifiedDate] = GetDate()  
      ,[Info1] = @Info1  
      ,[Info2] = @Info2  
      ,[Info3] = @Info3  
      ,[Info4] = @Info4  
      ,[Info5] = @Info5  
      ,[Info6] = @Info6  
      ,[Info7] = @Info7  
      ,[Info8] = @Info8  
      ,[Info9] = @Info9  
      ,[Info10] = @Info10  
      ,[Info11] = @Info11  
      ,[Info12] = @Info12  
      ,[Info13] = @Info13  
      ,[Info14] = @Info14  
      ,[Info15] = @Info15  
      ,[Info16] = @Info16  
      ,[Info17] = @Info17  
      ,[Info18] = @Info18  
      ,[Info19] = @Info19  
      ,[Info20] = @Info20
      ,[EInfo1] =  ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Email)
	  ,[EInfo2] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @Mobile) 
	  ,[EInfo3] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @OfficePostCode)
	  ,[EInfo4] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @HomePhone)
	  ,[EInfo5] = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), @OfficePhone)
    WHERE ContactId = @ContactId  
  
  SELECT @ReturnValue = @ContactId  
 END  
 
 Close Symmetric Key CXUserSym
 IF (@@ERROR != 0)  
 BEGIN  
  RETURN -1  
 END  
 ELSE  
 BEGIN  
  RETURN @ReturnValue  
 END  
  
 --=====================================================================
GO
ALTER PROCEDURE [dbo].[sprocUserContactSelectSingleItem]  
/* '===============================================================  
 '   NAME:                 [sprocUserContactSelectSingleItem]  
 '   DATE CREATED:        6 Oct 2009  
 '   CREATED BY:           Phyo Kyaw Thant  
 '   CREATED FOR:          ComparExpress.com - http://www.JobBase.com  
 '   FUNCTION:             Get the list of item from table: tblCEContact  
 '   IN:                    
 '   OUT:   Nothing           
 '   VERSION: 1             
 '   EXAMPLE:               
 '   COMMENTS:              
 '   MODIFIED ON:          31/Aug/2010
 '   MODIFIED BY:          Phyo Kyaw Thant
 '   REASON MODIFICATION:  TDE
 '===============================================================*/  
 @RefCEUserId bigint  
 AS  
  
 BEGIN  
 SET NOCOUNT ON  
  
    Open Symmetric Key CXUserSym
	Decryption By Certificate CXUserCert
	
  SELECT [ContactId]  
     ,CONVERT(nvarchar(255),DECRYPTBYKEY(EInfo1)) AS Email
     ,CONVERT(nvarchar(20),DECRYPTBYKEY(EInfo2)) AS Mobile
     ,[HomePostCode]  
     ,CONVERT(nvarchar(20),DECRYPTBYKEY(EInfo3)) AS OfficePostCode  
     ,[HomeUnitNo]  
     ,[OfficeUnitNo]  
     ,[HomeLevelNo]  
     ,[OfficeLevelNo]  
     ,[HomeStreet]  
     ,[OfficeStreet]  
     ,[HomeFax]  
     ,[OfficeFax]  
     ,CONVERT(nvarchar(20),DECRYPTBYKEY(EInfo4)) AS HomePhone
     ,CONVERT(nvarchar(20),DECRYPTBYKEY(EInfo5)) AS OfficePhone
     ,[HomeCity]  
     ,[HomeCountry]  
     ,[OfficeCity]  
     ,[OfficeCountry]  
     ,[Status]  
     ,[OtherContact1]  
     ,[OtherContact2]  
     ,[RefCEUserId]  
     ,[CreatedDate]  
     ,[ModifiedDate]  
     ,[Info1]  
     ,[Info2]  
     ,[Info3]  
     ,[Info4]  
     ,[Info5]  
     ,[Info6]  
     ,[Info7]  
     ,[Info8]  
     ,[Info9]  
     ,[Info10]  
     ,[Info11]  
     ,[Info12]  
     ,[Info13]  
     ,[Info14]  
     ,[Info15]  
     ,[Info16]  
     ,[Info17]  
     ,[Info18]  
     ,[Info19]  
     ,[Info20]  
    FROM [tblCEContact]  
   WHERE   
   ([RefCEUserId] = @RefCEUserId)  
   
   Close Symmetric Key CXUserSym
END  
  
  
  
  
  